<?php
namespace app\common;

class DB
{
    private static $dbConnections = [];
    
    private static $sqls = [];
    
    private static $lastSql;
    
    private $pdo;
    
    private $tmpTable;
    
    /**
     * 便捷初始化方法
     * @param string $policyId
     * @throws \RuntimeException
     * @return app\common\DB
     */
    public static function connect($policyId)
    {
        if (isset(self::$dbConnections[$policyId]))
        {
            return self::$dbConnections[$policyId];
        }
        $databaseConfigs = config('database');
        if (!isset($databaseConfigs[$policyId]))
        {
            throw new \RuntimeException("policyId: $policyId is not set in config.app.database");
        }
        $policy = $databaseConfigs[$policyId];
        
        $db = new static;
        $pdoDefaultOptions = [
            \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
            \PDO::ATTR_TIMEOUT => 1,
            \PDO::ATTR_CASE => \PDO::CASE_NATURAL,
            \PDO::ATTR_EMULATE_PREPARES => false,
            \PDO::ATTR_ORACLE_NULLS => \PDO::NULL_TO_STRING,
        ];
        
        if (!empty($policy['options']))
        {
            $pdoDefaultOptions = merge_args($pdoDefaultOptions, $policy['options']);
        }
        $pdo = new \PDO($policy['dsn'], $policy['user'], $policy['password'], $pdoDefaultOptions);
        $db->pdo = $pdo;
        self::$dbConnections[$policyId] = $db;
        return $db;
    }
    
    /**
     * 指定table
     * @param string $table
     * @return \app\common\DB
     */
    public function table($table)
    {
        $this->tmpTable = $table;
        return $this;
    }
    
    /**
     * 增加数据，插入，Create
     * @param array $data 如 ['name' => '张三', 'age' => 18] 或 [['name' => '张三', 'age' => 18], ['age' => '25', 'name' => 王五]]
     * @throws \InvalidArgumentException
     * @throws \PDOException
     * @return string 最后一条记录ID
     */
    public function insert(array $data)
    {
        if (is_null($this->tmpTable))
        {
            throw new \RuntimeException("call table() set the tableName first.");
        }
        if (empty($data) || !is_array($data))
        {
            throw new \InvalidArgumentException("data can't be empty and should be an array.");
        }
        $table = $this->tmpTable;
        if (count($data) === count($data, true))
        {
            $data = [$data];//一维数组，转化二维数组。统一当多条插入处理
        }
        $row = $data[0];//一条信息
        $fields = array_keys($row);//所有字段
        $placeHolderArr = array_fill(0, count($data), sprintf('(%s)', implode(',', array_fill(0, count($row), '?'))));
        $sql = $lastSql = sprintf("INSERT INTO `%s` (%s) VALUES %s", $table, self::parseFields($fields), implode(',', $placeHolderArr));
        $pdo = $this->pdo;
        try
        {
            $start = microtime(true);
            $stat = $pdo->prepare($sql);
            $i = 1;
            $bind = [];
            foreach ($data as $index => $item)
            {
                $itemBind = [];
                foreach ($fields as $field)
                {
                    if (!isset($item[$field]))
                    {
                        throw new \InvalidArgumentException(sprintf("index: %s, data: %s, lack of field: '%s'.", $index, json_encode($item, JSON_UNESCAPED_UNICODE) ,$field));
                    }
                    $itemBind[$i] = $item[$field];
                    $stat->bindValue($i++, $item[$field]);
                }
                $bind[] = $itemBind;
            }
    
            $lastSql = sprintf("sql: %s, bind: %s", $sql, json_encode($bind, JSON_UNESCAPED_UNICODE));
            self::$lastSql = $lastSql;
            $stat->execute();
            $lastSql .= sprintf(", time: %.2fms", (microtime(true) - $start) * 1000);
            self::$lastSql = $lastSql;
            self::$sqls[] = $lastSql;
            return $pdo->lastInsertId();
        }
        catch (\Exception $e)
        {
            $errClass = get_class($e);
            $message = sprintf("%s\n%s", $e->getMessage(), $lastSql);
            $code = stripos($e->getMessage(), 'SQLSTATE[') !== false ? hexdec($e->getCode()) : $e->getCode();
            $exception = new $errClass($message, $code, $e->getPrevious());
            throw $exception;
        }
    }
    
    /**
     * 通用查询，获取数据，Retrieve
     * @param array $args 如： ['fields' => '*', 'where' => 'age = :age', 'bind' => [':age' => 18], 'order' => 'id DESC', 'limit' => '0, 10', 'group' => 'uid', 'having' => 'count(score) > 60', 'object' => 'app\model\Comment']
     * @throws \RuntimeException
     * @return array
     */
    public function select($args = ['fields' => '*', 'where' => '', 'bind' => [], 'order' => '', 'limit' => '', 'group' => '', 'having' => '', 'object' => ''])
    {
        if (is_null($this->tmpTable))
        {
            throw new \RuntimeException("call table() set the tableName first.");
        }
        $table = $this->tmpTable;
        $defaults = ['fields' => '*', 'where' => '', 'bind' => [], 'order' => '', 'limit' => '', 'group' => '', 'having' => '', 'object' => ''];
        $args = merge_args($defaults, $args);
        $fields = self::parseFields($args['fields']);
        $sql = "SELECT $fields FROM `$table`";
        //where
        if (!empty($args['where']))
        {
            $sql .= " WHERE {$args['where']}";
        }
        //group
        if (!empty($args['group']))
        {
            $sql .= " GROUP BY {$args['group']}";
        }
        //having
        if (!empty($args['having']))
        {
            $sql .= " HAVING {$args['having']}";
        }
        //order
        if (!empty($args['order']))
        {
            $sql .= " ORDER BY {$args['order']}";
        }
        //limit
        if (!empty($args['limit']))
        {
            $sql .= " LIMIT {$args['limit']}";
        }
        return $this->fetchAll($sql, $args['bind'], $args['object']);
    }
    
    /**
     * 特殊查询，通过ID查找单条
     * @param array $args 如： ['id' => '12', 'fields' => '*', 'object' => 'app\Model\Comment']
     * @throws \RuntimeException
     * @throws \InvalidArgumentException
     * @return array|object|false 数据不存在时返回false
     */
    public function getById($args = ['id' => '', 'fields' => '*', 'object' => ''])
    {
        if (is_null($this->tmpTable))
        {
            throw new \RuntimeException("call table() set the tableName first.");
        }
        if ($args['id'] === '')
        {
            throw new \InvalidArgumentException(sprintf("%s needs id parameter.", __FUNCTION__));
        }
        $table = $this->tmpTable;
        $defaults = ['id' => '', 'fields' => '*', 'object' => ''];
        $args = merge_args($defaults, $args);
        if (!empty($args['fields']))
        {
            $fields = self::parseFields($args['fields']);
        }
        else
        {
            $fields = '*';
        }
        $sql = "SELECT $fields FROM `$table` WHERE `id` = :id LIMIT 1";
        $bind = [':id' => $args['id']];
        return $this->fetch($sql, $bind, $args['object']);
    }
    
    /**
     * 特殊查询，获取总数
     * @param array $args 如： ['count' => '*', 'where' => 'id > :id', 'bind' => [':id' => 90], 'group' => 'uid', 'having' => 'sum(salary) > 80']
     * @throws \RuntimeException
     * @return number
     */
    public function getCount($args = ['count' => '*', 'where' => '', 'bind' => [], 'group' => '', 'having' => ''])
    {
        if (is_null($this->tmpTable))
        {
            throw new \RuntimeException("call table() set the tableName first.");
        }
        $table = $this->tmpTable;
        $defaults = ['count' => '*', 'where' => '', 'bind' => [], 'group' => '', 'having' => ''];
        $args = merge_args($defaults, $args);
        
        $sql = "SELECT COUNT({$args['count']}) FROM `$table`";
        //where
        if (!empty($args['where']))
        {
            $sql .= " WHERE {$args['where']}";
        }
        //group
        if (!empty($args['group']))
        {
            $sql .= " GROUP BY {$args['group']}";
        }
        //having
        if (!empty($args['having']))
        {
            $sql .= " HAVING {$args['having']}";
        }
        $result = $this->fetch($sql, $args['bind']);
        return current($result);
    }
    
    /**
     * 特殊更新，通过ID更新单条
     * @param array $args 如： ['id' => '2', 'set' => 'age = :age', 'bind' => [':age' => 18]]
     * @throws \InvalidArgumentException
     * @return number 0 或 1
     */
    public function updateById($args = ['id' => '', 'set' => '', 'bind' => []])
    {
        if (empty($args['id']) || empty($args['set']))
        {
            throw new \InvalidArgumentException("parameter 'id' and 'set' could not be empty.");
        }
        $args['bind'][':id'] = $args['id'];
        $options = [
            'set' => $args['set'],
            'where' => 'id = :id',
            'bind' => $args['bind'],
            'limit' => 1,
        ];
        
        return $this->update($options);
    }
    
    /**
     * 通用更新，Update
     * @param array $args 如： ['set' => 'age = :age', 'where' => 'id >= :id', 'bind' => [':age' => 12, ':id' => 10], 'limit' => '1000']
     * @throws \RuntimeException
     * @throws \InvalidArgumentException
     * @return number 受影响条数
     */
    public function update($args = ['set' => '', 'where' => '', 'bind' => [], 'limit' => ''])
    {
        if (is_null($this->tmpTable))
        {
            throw new \RuntimeException("call table() set the tableName first.");
        }
        if ($args['set'] === '' || !is_string($args['set']))
        {
            throw new \InvalidArgumentException(sprintf("%s needs 'set' parameter and should be string.", __FUNCTION__));
        }
        $table = $this->tmpTable;
        $sql = "UPDATE `$table` SET {$args['set']}";
        if (!empty($args['where']))
        {
            $sql .= " WHERE {$args['where']}";
        }
        if (!empty($args['limit']))
        {
            $sql .= " LIMIT {$args['limit']}";
        }
        return $this->execute($sql, $args['bind']);
    }
    
    /**
     * 特殊删除，通过ID删除单条
     * @param array $args 如： ['id' => '3']
     * @return number 1 或 0
     */
    public function deleteById($args = ['id' => ''])
    {
        $options = [
            'where' => 'id = :id',
            'bind' => [':id' => $args['id']],
            'limit' => 1,
        ];
        return $this->delete($options);
    }
    
    /**
     * 通用删除，Delete
     * @param array $args 如： ['where' => 'id > :id', 'bind' => [':id' => 3], 'limit' => '1000']
     * @throws \RuntimeException
     * @throws \InvalidArgumentException
     * @return number 返回受影响条数
     */
    public function delete($args = ['where' => '', 'bind' => [], 'limit' => ''])
    {
        if (is_null($this->tmpTable))
        {
            throw new \RuntimeException("call table() set the tableName first.");
        }
        if ($args['where'] === '')
        {
            throw new \InvalidArgumentException(sprintf("%s needs where parameter.", __FUNCTION__));
        }
        $table = $this->tmpTable;
        $sql = "DELETE FROM `$table` WHERE {$args['where']}";
        if (!empty($args['limit']))
        {
            $sql .= " LIMIT {$args['limit']}";
        }
        return $this->execute($sql, $args['bind']);
    }
    
    /**
     * 获取最后一条sql
     * @return string
     */
    public function getLastSql()
    {
        return self::$lastSql;
    }
    
    /**
     * 获取所有sql
     * @return array
     */
    public function getAllSqls()
    {
        return self::$sqls;
    }
    
    /**
     * 获取一行
     * @param string $sql
     * @param array $bind
     * @param string $object
     * @throws \PDOException
     * @return array|object
     */
    public function fetch($sql, $bind, $object = '')
    {
        $lastSql = sprintf("sql: %s, bind: %s", $sql, json_encode($bind, JSON_UNESCAPED_UNICODE));
        self::$lastSql = $lastSql;
        try
        {
            $start = microtime(true);
            $stat = $this->pdo->prepare($sql);
            $stat->execute($bind);
            if (!empty($object))
            {
                $result = $stat->fetchObject($object);
            }
            else
            {
                $result = $stat->fetch(\PDO::FETCH_ASSOC);
            }
            $lastSql .= sprintf(", time: %.2fms", (microtime(true) - $start) * 1000);
            self::$lastSql = $lastSql;
            self::$sqls[] = $lastSql;
            return $result;
        }
        catch (\Exception $e)
        {
            $errClass = get_class($e);
            $message = sprintf("%s\n%s", $e->getMessage(), $lastSql);
            $code = stripos($e->getMessage(), 'SQLSTATE[') !== false ? hexdec($e->getCode()) : $e->getCode();
            $exception = new $errClass($message, $code, $e->getPrevious());
            throw $exception;
        }
    }
    
    /**
     * 获取多行
     * @param string $sql
     * @param array $bind
     * @param string $object
     * @throws \PDOException
     * @return array
     */
    public function fetchAll($sql, $bind, $object = '')
    {
        $lastSql = sprintf("sql: %s, bind: %s", $sql, json_encode($bind, JSON_UNESCAPED_UNICODE));
        self::$lastSql = $lastSql;
        try
        {
            $start = microtime(true);
            $stat = $this->pdo->prepare($sql);
            $stat->execute($bind);
            if (!empty($object))
            {
                $result = $stat->fetchAll(\PDO::FETCH_CLASS, $object);
            }
            else
            {
                $result = $stat->fetchAll(\PDO::FETCH_ASSOC);
            }
            $lastSql .= sprintf(", time: %.2fms", (microtime(true) - $start) * 1000);
            self::$lastSql = $lastSql;
            self::$sqls[] = $lastSql;
            return $result;
        }
        catch (\Exception $e)
        {
            $errClass = get_class($e);
            $message = sprintf("%s\n%s", $e->getMessage(), $lastSql);
            $code = stripos($e->getMessage(), 'SQLSTATE[') !== false ? hexdec($e->getCode()) : $e->getCode();
            $exception = new $errClass($message, $code, $e->getPrevious());
            throw $exception;
        }
    }
    
    /**
     * 执行非查询语句，如INSERT, UPDAYE, DELETE
     * @param string $sql
     * @param array $bind
     * @throws \PDOException
     * @return number 受影响条数
     */
    public function execute($sql, $bind)
    {
        $lastSql = sprintf("sql: %s, bind: %s", $sql, json_encode($bind, JSON_UNESCAPED_UNICODE));
        self::$lastSql = $lastSql;
        try
        {
            $start = microtime(true);
            $stat = $this->pdo->prepare($sql);
            $stat->execute($bind);
            $lastSql .= sprintf(", time: %.2fms", (microtime(true) - $start) * 1000);
            self::$lastSql = $lastSql;
            self::$sqls[] = $lastSql;
            return $stat->rowCount();
        }
        catch (\Exception $e)
        {
            $errClass = get_class($e);
            $message = sprintf("%s\n%s", $e->getMessage(), $lastSql);
            $code = stripos($e->getMessage(), 'SQLSTATE[') !== false ? hexdec($e->getCode()) : $e->getCode();
            $exception = new $errClass($message, $code, $e->getPrevious());
            throw $exception;
        }
    }
    
    /**
     * 获取原生PDO对象
     * @return \PDO
     */
    public function getPdo()
    {
        return $this->pdo;
    }
    
    public function transaction(\Closure $callback)
    {
        $this->pdo->beginTransaction();
        try
        {
            $result = $callback($this);
            $this->pdo->commit();
        }
        catch (\Exception $e)
        {
            $this->pdo->rollBack();
            throw $e;
        }
        return $result;
    }
    
    /**
     * 解析字段参数
     * @param string|array $fields
     * @throws \InvalidArgumentException
     * @return string
     */
    private static function parseFields($fields)
    {
        if (!empty($fields) && is_array($fields))
        {
            $fields = array_map(function($d) {return "`$d`";}, $fields);
            return implode(',', $fields);
        }
        elseif (!empty($fields) && is_string($fields))
        {
            return $fields;
        }
        else
        {
            throw new \InvalidArgumentException("Invalid fields: " . print_r($fields, true));
        }
    }
    
}